Function GetIPAddress()
    Const strComputer As String = "."   ' Computer name. Dot means local computer
    Dim objWMIService, IPConfigSet, IPConfig, IPAddress, i
    Dim strIPAddress As String

    ' Connect to the WMI service
    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

    ' Get all TCP/IP-enabled network adapters
    Set IPConfigSet = objWMIService.ExecQuery _
        ("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE")

    ' Get all IP addresses associated with these adapters
    For Each IPConfig In IPConfigSet
        IPAddress = IPConfig.IPAddress
        If Not IsNull(IPAddress) Then
            strIPAddress = strIPAddress & Join(IPAddress, ", ")
        End If
    Next

    GetIPAddress = strIPAddress
End Function



Sub t()
 Dim cn As ADODB.Connection
    Dim str As String
    Set cn = New ADODB.Connection
    
    cn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};" & "SERVER=10.7.21.57;" & "DATABASE=worklog;" & "UID=user;PWD=ncl@1234;OPTION=3"
    
    cn.Open
    
    If (cn.Execute("select count(0) from submitRecord where ipAddress = '" & GetIPAddress & "' and submitTime = '" & Date & "'").Fields.Item(0) >= 30) Then
        MsgBox "同一ip地址在一天内只能插入三次！日志导入失败"
        Exit Sub
    End If
    
    'Open "C:\tttt.txt" For Output As #1
  
    Set xlsheet = ThisWorkbook.ActiveSheet
    
    
    
    
  ' Automatically get the variable of start rows
  'MsgBox xlsheet.Cells(1, 1)
  For jj = 1 To 5
    If xlsheet.Cells(jj, 2).Value = "填报人" Then
        startrow = jj + 1
    End If
  
  Next
  
 ' MsgBox xlsheet.Cells(startrow, 3).Value
   thismonth = xlsheet.Cells(startrow, 3).Value
 
monthstring = Year(DateValue(thismonth)) & "-" & Month(DateValue(thismonth))
  
 a = "delete from worklog where name like '%" & xlsheet.Cells(startrow, 2) & "%' and date_format(time,'%Y-%m') = date_format(str_to_date('" & monthstring & "','%Y-%m'),'%Y-%m')"

 cn.Execute a
 
 
  '要求开发人员必须填写工作计划
  If (xlsheet.Cells(3, 4) = "开发") And (xlsheet.Cells(3, 8) = "" Or xlsheet.Cells(3, 9) = "" Or xlsheet.Cells(3, 10) = "") Then
    MsgBox "要求开发人员必须填写工作计划,日志导入失败"
    Exit Sub
  End If
 'MsgBox a
 'Print #1, a
' cn.Execute a
  
  personId = "0728"
  'loop the sheet to get all worklog into sql clause
  For i = startrow To 50
    
    ' if it's end of the content in this sheet then exit for loop
    If xlsheet.Cells(i, 4).Value = "" Then
        Exit For
    End If
    
    fsql = ""
    
    
    tsql = "insert into worklog(personId,companyName,name,time,workType,workDetail,requirements,startDate,endDate,daysNeed,progressPercentage,hoursToday,overworkHours,note) values "
    
    tsql = tsql & " ('" & personId & "',"

    flag = True
    
    ' a for loop to get all the sqls according to excel data
    
    For j = 1 To 13
        If xlsheet.Cells(i, j).Value = "" Then
            If j = 13 Then
                tsql = tsql & "null"
            Else
                tsql = tsql & "null,"
            End If
        Else
            If j = 13 Then
                tsql = tsql & "'" & Replace(Trim(xlsheet.Cells(i, j).Value), "'", "\'") & "'"
            Else
                tsql = tsql & "'" & Replace(Trim(xlsheet.Cells(i, j).Value), "'", "\'") & "',"
            End If
        End If
    
    Next
    
    tsql = tsql & ")"
    'MsgBox tsql
    'Print #1, tsql
     
    cn.Execute tsql
    
  Next
  

cn.Execute "insert into submitRecord (submitTime,ipAddress) values('" & Date & "','" & GetIPAddress & "')"

 
 MsgBox "日志导入完成当前页"
 
 'Close #1
 End Sub
